<?php

include 'DBconn.php';

class ProductSearch {

	public function getInOrderLinks($pro_serialnumber)
	{	
        $ret = array();
		
		$query1 = "Select pd.po_detserialnumber,ph.po_serialnumber,ph.po_id,ch.client_name as supplier,ph.po_issue_date,concat(ph.no_of_days,' Days') as eta,IFNULL(pd.pro_qty - IFNULL((Select IFNULL(Sum(gd.received_qty),0) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=pd.po_detserialnumber),0) ,0) as balance_qty,ph.supplier_serialnumber_fk, 
		ph.expected_datetime,sh.serialnumber,sh.customer_serialnumber_fk,IFNULL(chs.client_name,'Alliance Enterprices') as customer,sh.sales_con_code,sd.detserialnumber,sh.sales_con_date from po_details as pd LEFT JOIN sales_conformation_details as sd ON sd.detserialnumber=pd.sc_detserialnumber_fk LEFT JOIN sales_conformation_header as sh ON sh.activestatus=1 and 
		sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN client_header as chs ON chs.serialnumber=sh.customer_serialnumber_fk and chs.activestatus=1 LEFT JOIN po_header as ph ON ph.activestatus=1 and ph.po_serialnumber=pd.po_serialnumber_fk LEFT JOIN client_header as ch ON ch.activestatus=1 and ch.serialnumber=ph.supplier_serialnumber_fk LEFT JOIN currency_header as cur 
		ON cur.activestatus=1 and cur.serialnumber=ph.currency_serialnumber_fk where pd.pro_serialnumber_fk='$pro_serialnumber' and pd.activestatus=1 and pd.pro_status!='FULLY SATISFIED' and pd.po_serialnumber_fk Not In(Select po_serialnumber from po_header where activestatus=1 and po_status='DRAFT' or po_status='VOID' or po_status='FULLY SATISFIED') HAVING balance_qty > 0";
		$result1 = mysql_query($query1) or die(mysql_error());
        while ($row1 = mysql_fetch_object($result1)) 
		{
		    $temp = new VoProductInorderLinks();
			$temp->pro_serialnumber_fk = $pro_serialnumber;
			$temp->po_serialnumber = (int)$row1->po_serialnumber;
			$temp->po_detserialnumber = (int)$row1->po_detserialnumber;
			$temp->po_id = $row1->po_id;
			$temp->po_issue_date = date("d-m-Y", strtotime($row1->po_issue_date));
			$temp->po_expected_datetime = date("d-m-Y", strtotime($row1->expected_datetime));
			$temp->supplier_serialnumber_fk = (int)$row1->supplier_serialnumber_fk;
			$temp->supplier_name = $row1->supplier;
			$temp->sc_serialnumber = (int)$row1->serialnumber;
			$temp->sales_con_code = $row1->sales_con_code;
			$temp->sc_detserialnumber = (int)$row1->detserialnumber;
			$temp->sales_con_date = date("d-m-Y", strtotime($row1->sales_con_date));
			$temp->customer_serialnumber_fk = (int)$row1->customer_serialnumber_fk;
			$temp->customer_name = $row1->customer;
			$temp->balance_qty = (int)$row1->balance_qty;
			$temp->pro_qty = (int)$row1->balance_qty;
			$temp->eta = $row1->eta;
			$ret[] = $temp;	
		}		
       return $ret;
    }
	
	public function getOnSaleStockLinks($pro_serialnumber) 
	{
        $ret = array();
		
        $query1= "Select sh.serialnumber,sh.sales_con_code,sh.sales_con_date,sh.customer_serialnumber_fk,ch.client_name,sd.sc_serialnumber_fk,sd.detserialnumber,sd.pro_serialnumber_fk,sd.pro_qty,ph.pro_name,ph.part_number,ph.pro_description 
		from sales_conformation_details as sd LEFT JOIN product_header as ph ON ph.serialnumber=sd.pro_serialnumber_fk and ph.activestatus=1 LEFT JOIN sales_conformation_header as sh ON sh.serialnumber = sd.sc_serialnumber_fk 
		and sh.activestatus=1 LEFT JOIN client_header as ch ON ch.serialnumber=sh.customer_serialnumber_fk and ch.activestatus=1 where sd.pro_serialnumber_fk='$pro_serialnumber' and sd.activestatus=1 and sd.pro_status!='FULLY SATISFIED' 
		and sd.sc_serialnumber_fk Not In(Select serialnumber from sales_conformation_header as shs where shs.activestatus=1 and shs.customer_serialnumber_fk=0)";
		$result1 = mysql_query($query1) or die(mysql_error());
        while ($row1 = mysql_fetch_object($result1)) 
		{
			$balance_to_sale = 0;
			
			$query2 = "Select pms.map_serialnumber,pms.po_detserialnumber,pms.sc_detserialnumber,IFNULL(Sum(pms.allocated_qty-(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber)),0) as balance_qty,(Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.po_detserialnumber_fk=pms.po_detserialnumber and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber) as delivered_qty from po_map_sc as pms LEFT JOIN po_details as pd ON pd.activestatus=1 and pd.po_detserialnumber=pms.po_detserialnumber where pms.activestatus=1 and pms.sc_detserialnumber='$row1->detserialnumber' GROUP BY pms.po_detserialnumber HAVING balance_qty>0 ORDER BY pms.map_serialnumber";
			$s2 = mysql_query($query2) or die(mysql_error());
			while ($row2 = mysql_fetch_object($s2)) 
			{
				$reserved_qty = 0;
				 
				$query3 = "Select (IFNULL((Select IFNULL(Sum(gd.received_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.grn_detserialnumber_fk=gd.grn_detserialnumber),0))),0) from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=smp.po_detserialnumber),0) -  IFNULL((Select IFNULL(Sum(pms.allocated_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.sc_detserialnumber_fk=pms.sc_detserialnumber and gmd.po_detserialnumber_fk=pms.po_detserialnumber),0))),0) from po_map_sc as pms where pms.activestatus=1 and pms.po_detserialnumber=smp.po_detserialnumber and pms.self_sc=0 and pms.map_serialnumber<smp.map_serialnumber and pms.sc_detserialnumber IN(Select spms.sc_detserialnumber from po_map_sc as spms where spms.po_detserialnumber=smp.po_detserialnumber and spms.activestatus=1 and spms.self_sc=0 and (spms.sc_type=1 or spms.sc_type=3 or(spms.sc_type=2 and spms.map_serialnumber < (Select map_serialnumber from po_map_sc where activestatus=1 
				and self_sc=0 and sc_type=1 and po_detserialnumber=smp.po_detserialnumber and map_serialnumber>spms.map_serialnumber and map_serialnumber<=smp.map_serialnumber LIMIT 1))) and spms.map_serialnumber<smp.map_serialnumber)),0)) as on_stock,IFNULL((Select IFNULL(Sum(psc.allocated_qty-IFNULL((Select IFNULL(Sum(sgmd.do_pro_qty),0) from gr_do as sgmd where sgmd.activestatus=1 and sgmd.sc_detserialnumber_fk=smp.sc_detserialnumber and sgmd.po_detserialnumber_fk=smp.po_detserialnumber),0)),0) from po_map_sc as psc where psc.activestatus=1 and psc.sc_detserialnumber=smp.sc_detserialnumber and psc.po_detserialnumber=smp.po_detserialnumber),0) as balance_to_deliver from po_map_sc as smp where smp.activestatus=1 and smp.sc_detserialnumber='$row2->sc_detserialnumber' and smp.po_detserialnumber='$row2->po_detserialnumber' GROUP BY smp.sc_detserialnumber HAVING on_stock > 0 LIMIT 1";
				$s3 = mysql_query($query3) or die(mysql_error());
				if($row3 = mysql_fetch_object($s3))
				{
					$on_stock = (int)$row3->on_stock;
					$balance_to_deliver = (int)$row3->balance_to_deliver;
			
					if($balance_to_deliver <= $on_stock)
					{
						$reserved_qty = $balance_to_deliver;
					}else{$reserved_qty = $on_stock;}
				}			
			  $balance_to_sale = $balance_to_sale + $reserved_qty;
			}		
			
			if($balance_to_sale > 0)
			{
				$temp = new VoProductOnSaleLinks();			
				$temp->pro_serialnumber_fk = $pro_serialnumber;
				$temp->sc_serialnumber = (int)$row1->serialnumber;
				$temp->sales_con_code = $row1->sales_con_code;
				$temp->sc_detserialnumber = (int)$row1->detserialnumber;
				$temp->sales_con_date = date("d-m-Y", strtotime($row1->sales_con_date));
				$temp->customer_serialnumber_fk = (int)$row1->customer_serialnumber_fk;
				$temp->customer_name = $row1->client_name;	
				$temp->balance_to_sale = $balance_to_sale;
				$ret[] = $temp;
			}
		}
        return $ret;
    }
	
	public function getSoldLinks($pro_serialnumber) 
	{
        $ret = array();
		$compdate = date('Y-m-d', strtotime(date("Y-m-d", strtotime(date("Y-m-d"))) . " -30 day"));
        $query = "Select sh.serialnumber,sh.sales_con_code,sh.sales_con_date,sh.customer_serialnumber_fk,ch.client_name as customer,dd.delivery_order_fk,dh.delivery_order_code,dh.delivery_order_date,dd.delivered_qty
		from delivery_order_pro_details as dd LEFT JOIN delivery_order_header as dh ON dh.activestatus=1 and dh.serialnumber=dd.delivery_order_fk LEFT JOIN sales_conformation_details as sd ON sd.detserialnumber=dd.sc_detserialnumber_fk 
		and sd.activestatus=1 LEFT JOIN sales_conformation_header as sh	ON sh.activestatus=1 and sh.serialnumber=sd.sc_serialnumber_fk LEFT JOIN client_header as ch ON ch.serialnumber=sh.customer_serialnumber_fk and ch.activestatus=1 
		where dd.activestatus=1 and dd.cre_datetime >'$compdate' and dd.pro_serialnumber_fk='$pro_serialnumber'";
        $result = mysql_query($query) or die(mysql_error());
        while ($row = mysql_fetch_object($result)) 
		{
			$temp = new VoProductSoldLinks();
			$temp->pro_serialnumber_fk = $pro_serialnumber;
			$temp->sc_serialnumber = (int)$row->serialnumber;
			$temp->sales_con_code = $row->sales_con_code;
			$temp->sales_con_date = date("d-m-Y", strtotime($row->sales_con_date));
			$temp->customer_serialnumber_fk = (int)$row->customer_serialnumber_fk;
			$temp->customer_name = $row->customer;
			$temp->do_serialnumber = (int)$row->delivery_order_fk;
			$temp->delivery_order_code = $row->delivery_order_code;
			$temp->delivery_order_date = date("d-m-Y", strtotime($row->delivery_order_date));
			$temp->delivered_qty = (int) $row->delivered_qty;
            $ret[] = $temp;
        }
        return $ret;
    }

	 public function getOnHandStockLinks($pro_serialnumber) {
        $ret = array();
        $query1 = "Select pd.po_detserialnumber,ph.po_serialnumber,ph.po_id,ph.po_issue_date,ph.expected_datetime,ph.supplier_serialnumber_fk,ch.client_name as supplier,IFNULL((Select IFNULL(Sum(gd.received_qty-(IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 and gmd.grn_detserialnumber_fk=gd.grn_detserialnumber),0))),0) 
		from grn_detail as gd where gd.activestatus=1 and gd.po_detserialnumber_fk=pd.po_detserialnumber),0) - IFNULL(IFNULL((Select IFNULL(Sum(pms.allocated_qty),0) from po_map_sc as pms where pms.activestatus=1 and self_sc=0 and pms.po_detserialnumber=pd.po_detserialnumber),0)-IFNULL((Select IFNULL(Sum(gmd.do_pro_qty),0) from gr_do as gmd where gmd.activestatus=1 
		and gmd.po_detserialnumber_fk=pd.po_detserialnumber),0),0) as on_hand from po_details as pd LEFT JOIN po_header as ph ON ph.activestatus=1 and ph.po_serialnumber=pd.po_serialnumber_fk LEFT JOIN client_header as ch ON ch.activestatus=1 and ch.serialnumber=ph.supplier_serialnumber_fk where pd.activestatus=1 and pd.pro_serialnumber_fk='$pro_serialnumber' HAVING on_hand > 0";
        $result1 = mysql_query($query1) or die(mysql_error());
        while ($row1 = mysql_fetch_object($result1)) 
		{		
			$temp = new VoProductStockLinks();
			$temp->pro_serialnumber_fk = $pro_serialnumber;
			$temp->po_serialnumber = (int)$row1->po_serialnumber;
			$temp->po_id = $row1->po_id;
			$temp->po_issue_date = date("d-m-Y", strtotime($row1->po_issue_date));
			$temp->po_expected_datetime = date("d-m-Y", strtotime($row1->expected_datetime));
			$temp->supplier_serialnumber_fk = (int)$row1->supplier_serialnumber_fk;
			$temp->supplier_name = $row1->supplier;
			$temp->on_hand = (int) $row1->on_hand;
			$ret[] = $temp;
			
        }
        return $ret;
    }
}

class VoProductStockLinks 
{
	var $pro_serialnumber_fk;
	var $po_serialnumber;
	var $po_issue_date;
	var $po_expected_datetime;
    var $po_id;
	var $supplier_serialnumber_fk;
    var $supplier_name;
    var $on_hand;
}

class VoProductSoldLinks 
{
    var $pro_serialnumber_fk;
	var $sc_serialnumber;
    var $sales_con_code;
    var $sales_con_date;
	var $customer_serialnumber_fk;
	var $do_serialnumber;
    var $delivery_order_code;
	var $delivery_order_date;
    var $delivered_qty;
    var $customer_name;
}

class VoProductOnSaleLinks 
{
	var $pro_serialnumber_fk;
	var $sc_serialnumber;
	var $sales_con_code;
	var $sc_detserialnumber;
    var $sales_con_date;
	var $customer_serialnumber_fk;
	var $customer_name;
	var $balance_to_sale;
}

class VoProductInOrderLinks 
{
	var $pro_serialnumber_fk;
	var $po_serialnumber;
	var $po_detserialnumber;
    var $po_id;
    var $po_issue_date;
	var $po_expected_datetime;
    var $supplier_serialnumber_fk;
	var $supplier_name;
	var $sc_serialnumber;
    var $sales_con_code;
	var $sc_detserialnumber;
    var $sales_con_date;
	var $customer_serialnumber_fk;
	var $customer_name;
	var $pro_qty;
	var $balance_qty;
	var $eta;
}
// $m = new ProductSearch();
// $m->getOnSaleStockLinks(1);
?>